﻿using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Linq;
using System.Web;

namespace Codes {
    public class Forms {

        #region 表单分类
        public static string CreateTest_ReturnId(string Name, string Desc) {
            string resultid=string.Empty;
            string sql="insert into FromGroup([Name],[Desc],[AddTime]) values(@Name,@Desc,now())";
            if(Codes.AccessHelper.ExecuteNonQuery(connectionString.Content, sql, new OleDbParameter[]{
                new OleDbParameter("@Name", Name),
                new OleDbParameter("@Desc", Desc)
            })>0) {
                resultid= Codes.AccessHelper.ExecuteScalar(connectionString.Content, "select max(id) from FromGroup", null).ToString();
            }
            Codes.AccessHelper.ExecuteNonQuery(connectionString.Content, "create table [_FCU_"+resultid+"](id int primary key)");
            return resultid;
        }

        public static bool deleteMassGroup(string idList) {
            string sql="delete from FromGroup where ID in("+idList+")";
            int r= Codes.AccessHelper.ExecuteNonQuery(connectionString.Content, sql);
            System.Text.StringBuilder tname=new System.Text.StringBuilder();
            string[] _idlist=idList.Split(',');
            for(int i = 0;i < _idlist.Length;i++) {
                tname.Append("_FCU_"+ _idlist[i]+",");
            }
            tname.Remove(tname.Length-1, 1);
            Codes.AccessHelper.ExecuteNonQuery(connectionString.Content, "drop table "+tname);
            return r>0;
        }

        public static int getListCount() {
            string sql ="select count(1) from FromGroup";
            return (int)Codes.AccessHelper.ExecuteScalar(connectionString.Content, sql);
        }
        public static System.Data.DataTable getList(int pageindex, int pagesize) {
            int notTop=((pageindex-1)*pagesize);
            string sql=string.Empty;
            if(notTop==0) {
                sql="select top "+pagesize+" *,(select count(1) from fromTemp where GroupId=FromGroup.id) as titleCount from FromGroup order by [addtime] desc";
            } else {
                sql ="select top "+pagesize+" *,(select count(1) from fromTemp where GroupId=FromGroup.id) as titleCount from FromGroup where id not in(select top "+((pageindex-1)*pagesize)+" id from FromGroup) order by [addtime] desc";
            }
            return Codes.AccessHelper.ExecuteDataSet(connectionString.Content, sql).Tables[0];
        }

        public static System.Data.DataTable getInfo(string id) {
            string sql="select * from FromGroup where id="+id;
            return Codes.AccessHelper.ExecuteDataSet(connectionString.Content, sql).Tables[0];
        }
        public static bool changeGroup(string name,string desc, int ID) {
            string sql="update FromGroup set [name]=@name,[desc]=@desc where ID=@ID";
            return Codes.AccessHelper.ExecuteNonQuery(connectionString.Content, sql, new OleDbParameter[]{
                new OleDbParameter("@name", name),
                new OleDbParameter("@desc", desc),
                new OleDbParameter("@ID", ID)
            })>0;
        }
        #endregion

        #region 表单模版
        public static System.Data.DataTable getTemp(string id) {
            string sql="select * from FromTemp where id="+id;
            return Codes.AccessHelper.ExecuteDataSet(connectionString.Content, sql).Tables[0];
        }
        public static bool addTitle(string GroupId, string type, string Title, string Data, string Required, string Regx, string Display) {
            string sql="insert into FromTemp([GroupId],[Type],[Title],[Data],[Order],[Required],[Regx],[Display],[AddTime]) values(@GroupId,@Type,@Title,@Data,99,@Required,@Regx,@Display,now())";
            int ret= Codes.AccessHelper.ExecuteNonQuery(connectionString.Content, sql, new OleDbParameter[]{
                new OleDbParameter("@GroupId", GroupId),
                new OleDbParameter("@Type", type),
                new OleDbParameter("@Title", Title),
                new OleDbParameter("@Data", Data),
                new OleDbParameter("@Required", Required),
                new OleDbParameter("@Regx", Regx),
                new OleDbParameter("@Display", Display)
            });
            string vtype="varchar(255)";
            switch(type) {
                case "text":
                    vtype=string.Format("varchar({0})", 255);
                    break;
                case "textarea":
                case "full":
                    vtype=string.Format("text");
                    break;
            }
            string lastId= Codes.AccessHelper.ExecuteScalar(connectionString.Content, "select max(id) from FromTemp where GroupId="+GroupId).ToString();
            lastId=string.IsNullOrEmpty(lastId)?"1":lastId+"";
            Codes.AccessHelper.ExecuteNonQuery(connectionString.Content, "alter table _FCU_"+GroupId+" add column_"+(lastId)+" "+vtype);
            return ret>0;
        }

        public static bool changeTemp(string changeColm, string data, int ID) {
            string sql="update FromTemp set ["+changeColm+"]=@data where ID=@ID";
            return Codes.AccessHelper.ExecuteNonQuery(connectionString.Content, sql, new OleDbParameter[]{
                new OleDbParameter("@data", data),
                new OleDbParameter("@ID", ID)
            })>0;
        }

        public static bool changeTemp(string title, string data, string display, string required, string regx, int ID) {
            string sql="update FromTemp set [Title]=@title,[Data]=@data,[Regx]=@regx,[Display]="+display+",[Required]="+required+" where ID=@ID";

            return Codes.AccessHelper.ExecuteNonQuery(connectionString.Content, sql, new OleDbParameter[]{
                new OleDbParameter("@title", title),
                new OleDbParameter("@data", data),
                new OleDbParameter("@regx", regx),
                new OleDbParameter("@ID", ID)
            })>0;
        }

        public static bool deleteMassTemp(string GroupId, string idList) {
            string sql="delete from FromTemp where ID in("+idList+")";
            int r= Codes.AccessHelper.ExecuteNonQuery(connectionString.Content, sql);
            System.Text.StringBuilder columns=new System.Text.StringBuilder();
            string[] _idlist=idList.Split(',');
            for(int i = 0;i < _idlist.Length;i++) {
                columns.Append("column_"+ _idlist[i]+",");
            }
            columns.Remove(columns.Length-1, 1);
            Codes.AccessHelper.ExecuteNonQuery(connectionString.Content, "ALTER TABLE _FCU_"+GroupId+" DROP COLUMN "+columns);
            return r>0;
        }
        public static int getTempListCount(string groupid) {
            string sql ="select count(1) from FromTemp where [GroupId]="+groupid;
            return (int)Codes.AccessHelper.ExecuteScalar(connectionString.Content, sql);
        }
        public static System.Data.DataTable getTempList(string groupid, int pageindex, int pagesize) {
            int notTop=((pageindex-1)*pagesize);
            string sql=string.Empty;
            if(notTop==0) {
                sql="select top "+pagesize+" * from FromTemp where [GroupId]="+groupid+" order by [order] asc";
            } else {
                sql ="select top "+pagesize+" * from FromTemp where id not in(select top "+((pageindex-1)*pagesize)+" id from FromTemp where [GroupId]="+groupid+") order by [order] asc";
            }
            return Codes.AccessHelper.ExecuteDataSet(connectionString.Content, sql).Tables[0];
        }

        #endregion


        #region 提交表单数据
        public static bool addData(string userid, string data, string groupId) {
            string sql="insert into FromUserData(UserID,Data,GroupId,AddTime) values(UserID,Data,GroupId,now())";
            return Codes.AccessHelper.ExecuteNonQuery(connectionString.Content, sql, new OleDbParameter[]{
                new OleDbParameter("@UserID", userid),
                new OleDbParameter("@Data", data),
                new OleDbParameter("@GroupId", groupId)
            })>0;
        }
        public static int getDataListCount(string groupid) {
            string sql ="select count(1) from FromUserData where [GroupId]="+groupid;
            return (int)Codes.AccessHelper.ExecuteScalar(connectionString.Content, sql);
        }
        public static bool deleteMassData(string idList) {
            string sql="delete from FromUserData where ID in("+idList+")";
            return Codes.AccessHelper.ExecuteNonQuery(connectionString.Content, sql)>0;
        }
        public static System.Data.DataTable getDataList(string groupid, int pageindex, int pagesize) {
            int notTop=((pageindex-1)*pagesize);
            string sql=string.Empty;
            if(notTop==0) {
                sql="select top "+pagesize+" * from FromUserData where [GroupId]="+groupid+" order by [order] asc";
            } else {
                sql ="select top "+pagesize+" * from FromUserData where id not in(select top "+((pageindex-1)*pagesize)+" id from FromUserData where [GroupId]="+groupid+") order by [order] asc";
            }
            return Codes.AccessHelper.ExecuteDataSet(connectionString.Content, sql).Tables[0];
        }
        #endregion
    }
}